"""
sql-call-graph-generator generates call-graphs for SQL stored routines

The paths to the graphviz executables are hardcoded, although this shouldn't be necessary

Usage: python sql_call_graph_generator.py host, user, password, database, outputgraph.png

The script then pulls the routines with their type and code from the database.
It then scans their code for references to other routines and builds a graph from that. 
"""

from database import DataBase
from optparse  import OptionParser

def findDefinitionName(query):
    funcNameEnd = query.find("`(") #after a function definition come the parameters, so a ( is used
    funcNameStart = query.rfind(".`",0,funcNameEnd) #the functionname starts after `databasename`.` rfind is used to find the last index of that substring
    return query[funcNameStart+2:funcNameEnd]

def findReferencedName(line):
    #finds the name of the routine that is referenced in the given line
    funcNameEnd = line.find("(") #after a function definition come the parameters, so a ( is used
    funcNameStart = line.rfind(" ",0,funcNameEnd) #the functionname starts after `databasename`.` rfind is used to find the last index of that substring

    funcName = line[funcNameStart+1:funcNameEnd]
    if funcName != '':
        return funcName

def findReferencedRoutines(query):
    lines = query.splitlines()
    callingLines = (line for line in lines if line.count('(') > 0) #get all lines that contain a ( and thus possibly call a routine

    referencedNames = [findReferencedName(line) for line in callingLines]
    return referencedNames#[:1]

def genDotEdges(references, includeBuiltin=True):
    edges = []
    for source, destinations in references.items():
        for dst in destinations:#[1:]: #skip the first one, which is a reference to itself.
            if dst and (dst in references.keys()): #if not '' and in the list of routines and aren't ignored
                print "Processing call from %s to %s..." %(source, dst)
                edge = (source, dst)
                edges.append(edge)
    return edges

def genGraph(edges, path, prog='dot', format='png'):
    import pydot
    pydot.find_graphviz()
    g=pydot.graph_from_edges(edges, directed=True)
    g.set_graphviz_executables({'dot': 'C:\\Program Files\\Graphviz2.16\\bin\\dot.exe', 'twopi': 'C:\\Program Files\\Graphviz2.16\\bin\\twopi.exe', 'neato': 'C:\\Program Files\\Graphviz2.16\\bin\\neato.exe', 'circo': 'C:\\Program Files\\Graphviz2.16\\bin\\cirro.exe', 'fdp': 'C:\\Program Files\\Graphviz2.16\\bin\\fdp.exe'})

    writes = {'png':g.write_png, 'svg':g.write_svg, 'dot':g.write_dot}
    return writes[format](path, prog=prog)
    #return g.write_png(path, prog=prog)

if __name__ == "__main__":
    import sys
    parser = OptionParser(usage="%prog -s server -u root -p rootpass --db dbwithroutines -f dbgraph --format png -i MAX+COUNT+UserFunction+UserProcedure+MIN")
    #required arguments are:
    #server, user, password, database
    parser.add_option("--server",    "-s",   dest="server",  help="The server to connect to")
    parser.add_option("--user",      "-u",   dest="user",    help="The user to login with")
    parser.add_option("--password",  "-p",   dest="password",help="The password for the specified user")
    parser.add_option("--database",  "--db",  dest="database",help="The database to use")

    #options are:
    #filename, format, ignores

    parser.add_option("--file",      "-f",   dest="filename",    help="File to store graph to. Defaults to the option DATABASE")
    parser.add_option("--format",    "--for", dest="format",   default="png",         help="The output-format to use. Currently implemented are png, svg, dot. Default is png")
    parser.add_option("--ignore",    "-i",  dest="ignore",   help="Ignore routines with the given names while building the graph")

    (options, args) = parser.parse_args()

    print "options %s" %(options)
    
    if not options.filename:
        print "Defaulting filename to database"
        options.filename = options.database
    filename = options.filename+"."+options.format
##    if len(sys.argv) > 6:
##        server, user, password, database, filename = sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4], sys.argv[5]
##    else:
##        print "Not enough parameters given. Specify host, user, password, database, outputgraph.png [ignore]"
##        sys.exit(2)

    ignoreNames = options.ignore.split("+")
    print "Ignorelist: %s" %ignoreNames
        
    d = DataBase(options.server, options.user, options.password, options.database)
    routines = d.returnRoutinesWithCode(ignoreNames)

    #references = {name:findReferencedRoutines(routines[name]) for name in routines.keys()} #Python3.0
    references = {}
    for name in routines.keys():
        references[name] = findReferencedRoutines(routines[name])

    print("Constructing graph")
    edges = genDotEdges(references, routines)

    print("Generating graph...")
    if genGraph(edges, filename, format=options.format):
        print("Succeeded, outputed file is %s" %filename)
    else:
        print("Failed")
    
    
